﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cmsservice.Entity;
using System.Data;

namespace cmsservice.DAL
{
    public class MiniTrainDAL
    {
        /// <summary>
        /// 取特定条数的今天及以后的会场记录
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        public List<TrainEnty> GetTopTrain(int num)
        {
            string sql = "select top " + num + " * from train where type='ZXHC' " +
                        " order by begintime asc";
            //Tools tool = new Tools();
            //tool.AddSqlParameter("time", SqlDbType.DateTime, DateTime.Today);
            return SqlHelper.GetDataByColumnsLst<TrainEnty>(DBConn.TemporaryDB, sql, null);
        }

        /// <summary>
        /// 根据id取网络会场的网址和密码
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TrainEnty GetAddressByTrainId(string id)
        {
            string sql = "select * from train where tid=@tid";
            Tools tool = new Tools();
            tool.AddSqlParameter("tid", SqlDbType.VarChar, id);
            return SqlHelper.GetDataByColumns<TrainEnty>(DBConn.TemporaryDB, sql, tool.GetSqlParameters());
        }

        /// <summary>
        /// 根据地区取有效期大于等于今天的最新一条地面会场信息
        /// </summary>
        /// <param name="area"></param>
        /// <returns></returns>
        public DataSet GetZJTrain(string area)
        {
            try
            {
                Tools tool = new Tools();
                int sign = 2;
                tool.AddSqlParameter("@sign", SqlDbType.Int, sign);
                tool.AddSqlParameter("@id", SqlDbType.Int, null);
                tool.AddSqlParameter("@area", SqlDbType.VarChar, area);
                tool.AddSqlParameter("@placenum", SqlDbType.VarChar, "");
                DataSet ds = SqlHelper.ExecuteGetDataSet(DBConn.emweb_new, CommandType.StoredProcedure, "web_TrainPlace_Select", tool.GetSqlParameters());
                return ds;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据id获取地面会场信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetZJMDMById(string id)
        {
            Tools tool = new Tools();
            int sign = 1;
            tool.AddSqlParameter("@sign", SqlDbType.Int, sign);
            tool.AddSqlParameter("@id", SqlDbType.Int, Convert.ToInt32(id));
            tool.AddSqlParameter("@area", SqlDbType.VarChar, "");
            tool.AddSqlParameter("@placenum", SqlDbType.VarChar, "");
            DataSet ds = SqlHelper.ExecuteGetDataSet(DBConn.emweb_new, CommandType.StoredProcedure, "web_TrainPlace_Select", tool.GetSqlParameters());
            return ds;
        }

        /// <summary>
        /// 预订地面会场
        /// </summary>
        /// <param name="username"></param>
        /// <param name="area"></param>
        /// <param name="placenum"></param>
        /// <param name="reservenum"></param>
        /// <param name="isattend"></param>
        /// <returns></returns>
        public string InsertUserInfo(string username, string area, string placenum, string reservenum, int isattend)
        {
            Tools tool = new Tools();
            int sign = 0;
            tool.AddSqlParameter("@sign", SqlDbType.Int, sign);
            tool.AddSqlParameter("@id", SqlDbType.Int, null);
            tool.AddSqlParameter("@username", SqlDbType.VarChar, username);
            tool.AddSqlParameter("@area", SqlDbType.VarChar, area);
            tool.AddSqlParameter("@reservenum", SqlDbType.VarChar, reservenum);
            tool.AddSqlParameter("@placenum", SqlDbType.VarChar, placenum);
            tool.AddSqlParameter("@isattend", SqlDbType.Int, isattend);
            DataSet ds = SqlHelper.ExecuteGetDataSet(DBConn.emweb_new, CommandType.StoredProcedure, "web_TrainUser_InsAndSelect", tool.GetSqlParameters());
            return ds.Tables[0].Rows[0][0].ToString();

        }

        /// <summary>
        /// 推荐视频
        /// </summary>
        /// <returns></returns>
        public DataSet GetTJSP(int num)
        {
            Tools tool = new Tools();
            int sign = 5;
            tool.AddSqlParameter("@sign", SqlDbType.Int, sign);
            tool.AddSqlParameter("@v_id", SqlDbType.Int, null);
            tool.AddSqlParameter("@classid", SqlDbType.Int, null);
            tool.AddSqlParameter("@topnum", SqlDbType.Int, num);
            DataSet ds = SqlHelper.ExecuteGetDataSet(DBConn.emweb_new, CommandType.StoredProcedure, "web_video_select_w", tool.GetSqlParameters());
            return ds;
        }

        /// <summary>
        /// 得到所有的成功启示录
        /// 乾坤风向标
        /// </summary>
        /// <returns></returns>
        public DataSet Windvacelst()
        {
            string sql = "select top 1 * from tb_Success order by InsertTime desc";
            DataSet ds = SqlHelper.ExecuteGetDataSet(DBConn.emweb_new, CommandType.Text, sql, null);
            return ds;
        }

        public List<UcTrainEnty> GetUcTrainByArea(string area, int flag, int id)
        {
            Tools tool = new Tools();
            List<UcTrainEnty> lst = new List<UcTrainEnty>();
            DataTable dt = null;
            tool.AddSqlParameter("area", SqlDbType.VarChar, 50, area);
            tool.AddSqlParameter("flag", SqlDbType.Int, flag);
            tool.AddSqlParameter("id", SqlDbType.Int, id);

            try
            {
                dt = SqlHelper.ExecuteGetTable(DBConn.emweb_new, CommandType.StoredProcedure, "GetTraingInfoByArea", tool.GetSqlParameters());
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    UcTrainEnty m = new UcTrainEnty();
                    if (i == 4) { break; }
                    m.N_id = Tools.GetInt(dt.Rows[i]["n_id"]);
                    m.N_title = Tools.GetString(dt.Rows[i]["n_title"]);
                    m.N_content = Tools.GetString(dt.Rows[i]["n_content"]);
                    lst.Add(m);
                }

            }
            catch
            {
                lst = null;
            }
            return lst;

        }

        public List<SpImgEnty> GetSpImg(int id, int classid)
        {
            Tools tool = new Tools();
            List<SpImgEnty> lst = new List<SpImgEnty>();
            DataTable dt = null;
            tool.AddSqlParameter("id", SqlDbType.Int, 4, id);
            tool.AddSqlParameter("classid", SqlDbType.Int, 4, classid);           

            try
            {
                dt = SqlHelper.ExecuteGetTable(DBConn.emweb_new, CommandType.StoredProcedure, "[web_video_img_GetElementByvidAndClassid]", tool.GetSqlParameters());
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SpImgEnty m = new SpImgEnty();
                    m.Id = Tools.GetString(dt.Rows[0]["id"]);
                    m.V_img = Tools.GetString(dt.Rows[0]["v_img"]);
                    m.V_id = Tools.GetInt(dt.Rows[0]["v_id"]);
                    m.V_classid = Tools.GetInt(dt.Rows[0]["v_classid"]);
                    lst.Add(m);
                }

            }
            catch
            {
                lst = null;
            }
            return lst;
        }
    }
}
